﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet;
using QL_Truong_Hoc;

namespace QL_Truong_Hoc.Export
{
    class clsDiemVanHoa4Mon
    {

        //Data table đầu vào cần có những trường sau:
        //khs_tenkhoas
        //LVH_ID
        //LVH_ten
        //hs_hodem
        //hs_ten
        //hs_ngaysinh

        //Lop10_HS11
        //Lop10_HS12
        //Lop10_HS13
        //Lop10_HS21
        //Lop10_HS22
        //Lop10_HS23
        //Lop10_HS3

        //Lop11_HS11
        //Lop11_HS12
        //Lop11_HS13
        //Lop11_HS21
        //Lop11_HS22
        //Lop11_HS23
        //Lop11_HS3

        //Lop12_HS11
        //Lop12_HS12
        //Lop12_HS13
        //Lop12_HS21
        //Lop12_HS22
        //Lop12_HS23
        //Lop12_HS3

        private string mcstrTemplate = Environment.CurrentDirectory + @"\Templates\Mau 06.8.5_Diem tong ket ca nam 4 mon Van hoa.xls";
        private bool mblnDocumentLoaded = false;
        private bool mblnInsertTongKet = false;

        public clsDiemVanHoa4Mon()
        {
        }


        /// <summary>
        /// Thực hiện xuất ra file excel.
        /// File template là file đã định sẵn.
        /// </summary>
        /// <param name="objData">Dữ liệu đưa vào file word</param>
        public bool fncExport(DataTable objData)
        {
            bool blnResult = false;

            try
            {
                //get data
                SpreadsheetControl objExcel = new SpreadsheetControl();
                objExcel.LoadDocument(mcstrTemplate);

                //xExport(objExcel, objData, false);

                return true;
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }


        /// <summary>
        /// Thực hiện xuất ra file excel.
        /// </summary>
        /// <param name="SpreadsheetControl">excel word</param>
        /// <param name="objData">Dữ liệu đầu vào</param>
        /// <param name="blnShowMergedData">Có hiện dữ liệu đã xuất ngay trên control không?</param>
        /// <returns></returns>
        public bool fncExport(SpreadsheetControl objExcelControl, DataTable objData, clsEnum.MON_VAN_HOA emMon, bool blnShowMergedData = false)
        {
            bool blnResult = false;

            try
            {
                //get data
                return xExport(objExcelControl, objData, emMon, blnShowMergedData);
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }


        /// <summary>
        /// Phương thức cơ sở thực hiện mail merge
        /// </summary>
        /// <param name="objExcelControl">control excel</param>
        /// <param name="objData">dữ liệu vào</param>
        /// <param name="blnShowMergedData">có xem kết quả không?</param>
        /// <returns></returns>
        private bool xExport(SpreadsheetControl objExcelControl, DataTable objData, clsEnum.MON_VAN_HOA emMon, bool blnShowMergedData = false)
        {
            bool blnResult = false;

            try
            {
                if (objData == null) return false;

                if (!mblnDocumentLoaded)
                {
                    if (!System.IO.File.Exists(mcstrTemplate))
                    {
                        clsCommon.fncMsgWarning("Không tìm thấy tệp tin mẫu.");
                        return false;
                    }

                    objExcelControl.LoadDocument(mcstrTemplate);
                    mblnDocumentLoaded = true;
                }

                string strkhs_tenkhoas = "";
                string strLVH_ten = "";
                string strhs_hodem;
                string strhs_ten;
                DateTime dths_ngaysinh;

                float fltLop10_HS11;
                float fltLop10_HS12;
                float fltLop10_HS13;
                float fltLop10_HS21;
                float fltLop10_HS22;
                float fltLop10_HS23;
                float fltLop10_HS3;
                float fltLop10_Tongket;

                float fltLop11_HS11;
                float fltLop11_HS12;
                float fltLop11_HS13;
                float fltLop11_HS21;
                float fltLop11_HS22;
                float fltLop11_HS23;
                float fltLop11_HS3;
                float fltLop11_Tongket;

                float fltLop12_HS11;
                float fltLop12_HS12;
                float fltLop12_HS13;
                float fltLop12_HS21;
                float fltLop12_HS22;
                float fltLop12_HS23;
                float fltLop12_HS3;
                float fltLop12_Tongket;

                float fltCaNam_Tongket;

                int intCurrentRow = 8;
                int intCurrentCol = 0;

                string strtWorkSheet = "Toan";

                const string cstrFloatFormat = "0.0";

                switch (emMon)
                {
                    case clsEnum.MON_VAN_HOA.TOAN: strtWorkSheet = "Toan"; break;
                    case clsEnum.MON_VAN_HOA.LY: strtWorkSheet = "Ly"; break;
                    case clsEnum.MON_VAN_HOA.HOA: strtWorkSheet = "Hoa"; break;
                    case clsEnum.MON_VAN_HOA.VAN: strtWorkSheet = "Van"; break;
                }
                
                //tạo n dong moi.
                if (objData.Rows.Count > 3)
                {
                    objExcelControl.Document.Worksheets[strtWorkSheet].Rows.Insert(10, objData.Rows.Count - 3);

                    //(12 + objData.Rows.Count - 2) -> bắt đầu tính từ dòng 12 + (Số bản ghi - 1 là 2 dòng đầu tiên bị nhảy qua + 1 dòng cuối ko copy format)
                    objExcelControl.Document.Worksheets[strtWorkSheet].Range["A11:AD" + (10 + objData.Rows.Count - 3)].CopyFrom(objExcelControl.Document.Worksheets[strtWorkSheet].Range["A10:AD10"]);

                    if (!mblnInsertTongKet)
                    {
                        //xóa dòng thừa trong sheet tổng kết năm
                        objExcelControl.Document.Worksheets["TK Nam"].Range["A" + (8 +objData.Rows.Count) +  ":U200"].Delete(DeleteMode.EntireRow);

                        mblnInsertTongKet = true;
                    }
                }

                //lấy dòng đầu tiên fill header
                if (objData.Rows.Count > 0)
                {
                    strkhs_tenkhoas = clsCommon.fncCnvNullToString(objData.Rows[0]["khs_tenkhoas"]);
                    strLVH_ten = clsCommon.fncCnvNullToString(objData.Rows[0]["LVH_ten"]);
                }

                objExcelControl.Document.Worksheets[strtWorkSheet].Cells[3, 0].Value = string.Format("BẢNG ĐIỂM LỚP VĂN HÓA {0}: LỚP {1}", strkhs_tenkhoas, strLVH_ten.ToUpper());

                for (int i = 0; i <= objData.Rows.Count - 1; i++)
                {
                    //lay data
                    strhs_hodem = clsCommon.fncCnvNullToString(objData.Rows[i]["hs_hodem"]);
                    strhs_ten = clsCommon.fncCnvNullToString(objData.Rows[i]["hs_ten"]);
                    dths_ngaysinh = clsCommon.fncCnvNullToDatetime(objData.Rows[i]["hs_ngaysinh"]);

                    fltLop10_HS11 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS11"]);
                    fltLop10_HS12 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS12"]);
                    fltLop10_HS13 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS13"]);
                    fltLop10_HS21 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS21"]);
                    fltLop10_HS22 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS22"]);
                    fltLop10_HS23 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS23"]);
                    fltLop10_HS3 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_HS3"]);
                    fltLop10_Tongket = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop10_diemtongket"]);
                    //fltLop10_Tongket = clsCommon.fncDiemTongKetVanHoa(fltLop10_HS11, fltLop10_HS12, fltLop10_HS13, fltLop10_HS21, fltLop10_HS22, fltLop10_HS23, fltLop10_HS3);

                    fltLop11_HS11 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS11"]);
                    fltLop11_HS12 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS12"]);
                    fltLop11_HS13 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS13"]);
                    fltLop11_HS21 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS21"]);
                    fltLop11_HS22 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS22"]);
                    fltLop11_HS23 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS23"]);
                    fltLop11_HS3 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_HS3"]);
                    fltLop11_Tongket = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop11_diemtongket"]);
                    //fltLop11_Tongket = clsCommon.fncDiemTongKetVanHoa(fltLop11_HS11, fltLop11_HS12, fltLop11_HS13, fltLop11_HS21, fltLop11_HS22, fltLop11_HS23, fltLop11_HS3);

                    fltLop12_HS11 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS11"]);
                    fltLop12_HS12 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS12"]);
                    fltLop12_HS13 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS13"]);
                    fltLop12_HS21 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS21"]);
                    fltLop12_HS22 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS22"]);
                    fltLop12_HS23 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS23"]);
                    fltLop12_HS3 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_HS3"]);
                    fltLop12_Tongket = clsCommon.fncCnvNullToFloat(objData.Rows[i]["Lop12_diemtongket"]);
                    //fltLop12_Tongket = clsCommon.fncDiemTongKetVanHoa(fltLop12_HS11, fltLop12_HS12, fltLop12_HS13, fltLop12_HS21, fltLop12_HS22, fltLop12_HS23, fltLop12_HS3);

                    fltCaNam_Tongket = clsCommon.fncDiemCaNamVanHoa(fltLop10_Tongket, fltLop11_Tongket, fltLop12_Tongket);

                    //set cell
                    intCurrentCol = 0; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = i + 1;
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = strhs_hodem;
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = strhs_ten;
                    intCurrentCol++; if (dths_ngaysinh > DateTime.MinValue) objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = dths_ngaysinh.ToString("dd/MM/yyyy");

                    //lớp 10
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS11.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS12.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS13.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS21.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS22.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS23.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_HS3.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop10_Tongket.ToString(cstrFloatFormat);

                    //lớp 11
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS11.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS12.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS13.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS21.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS22.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS23.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_HS3.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop11_Tongket.ToString(cstrFloatFormat);

                    //lớp 12
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS11.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS12.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS13.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS21.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS22.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS23.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_HS3.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltLop12_Tongket.ToString(cstrFloatFormat);

                    //cả năm
                    intCurrentCol++; objExcelControl.Document.Worksheets[strtWorkSheet].Cells[intCurrentRow, intCurrentCol].Value = fltCaNam_Tongket.ToString(cstrFloatFormat);

                    intCurrentRow++;
                }
                
                ////file to save
                //SaveFileDialog dlgSave = new SaveFileDialog();
                //dlgSave.Filter = "Excel (*.xls)|*.xls|All files (*.*)|*.*";
                //dlgSave.FilterIndex = 1;
                //dlgSave.RestoreDirectory = true;

                ////export word
                //objExcelControl.SaveDocument(dlgSave.FileName, DocumentFormat.Xls);

                return true;
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }

    }
}
